﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using FOModels.HomeLoanTrust;
namespace DataAccess.HomeLoanTrust
{
    public class HomeLoanTrustDAL
    {
        private SQLDataAccess db;
        public HomeLoanTrustDAL()
        {
            db = new SQLDataAccess();
        }
        public DataTable getTOP1()
        {
            return db.exeSelect("Select TOP 1 * from HomeLoan order by HomeLoanID desc");
        }
        public DataTable getHomeLoanPlan(int langid)
        {
            return db.exeSelect("Select * from HomeLoanPlan h join HomeLoanPlanLang hl on h.HomeLoanPlanID = hl.HomeLoanPlanID where LangID = " + langid + " order by hl.HomeLoanPlanID desc");
        }
        public DataTable getHomeLoanAll(int cateid, int planid)
        {
            return db.exeSelect("Select * from HomeLoan sa join Company co on sa.CompanyID = co.CompanyID where co.Active = 1 and sa.HomeLoanCategoryID =" + cateid + " and sa.HomeLoanPlanID =" + planid + " ");
        }
        public DataTable getHomeLoanDefault(int cateid, int langid)
        {
            return db.exeSelect("Select * from HomeLoan sa join HomeLoanLang hl on sa.HomeLoanID = hl.HomeLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableHomeLoanEquityInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.HomeLoanCategoryID =" + cateid + " and sa.HomeLoanPlanID = 2 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and ta.Years = 1 and Display = 1 and hl.LangID= " + langid + " and cl.LangID = " + langid + "");
        }
        public DataTable getHomeLoanMortgageDefault(int cateid, int langid)
        {
            return db.exeSelect("Select * from HomeLoan sa join HomeLoanLang hl on sa.HomeLoanID = hl.HomeLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableHomeLoanMortgageInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.HomeLoanCategoryID =" + cateid + " and sa.HomeLoanPlanID = 2 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and ta.Years = 1 and Display = 1 and hl.LangID= " + langid + " and cl.LangID = " + langid + "");
        }
        public DataTable getHomeLoanDefaultByProvider(int cateid, int CompanyID, int langid)
        {
            return db.exeSelect("Select * from HomeLoan sa join HomeLoanLang hl on sa.HomeLoanID = hl.HomeLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableHomeLoanEquityInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.HomeLoanCategoryID =" + cateid + " and sa.HomeLoanPlanID = 2 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and ta.Years = 1 and Display = 1 and hl.LangID= " + langid + " and cl.LangID = " + langid + " and sa.CompanyID=" + CompanyID + "");
        }
        public DataTable getHomeLoanMortgageDefaultByProvider(int cateid, int CompanyID, int langid)
        {
            return db.exeSelect("Select * from HomeLoan sa join HomeLoanLang hl on sa.HomeLoanID = hl.HomeLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableHomeLoanMortgageInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.HomeLoanCategoryID =" + cateid + " and sa.HomeLoanPlanID = 2 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and ta.Years = 1 and Display = 1 and hl.LangID= " + langid + " and cl.LangID = " + langid + " and sa.CompanyID=" + CompanyID + "");
        }
        public DataTable getHomeLoan(int cateid, int planid, string balance, int years, int langid)
        {
            return db.exeSelect("Select * from HomeLoan sa join HomeLoanLang hl on sa.HomeLoanID = hl.HomeLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableHomeLoanEquityInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.HomeLoanCategoryID =" + cateid + " and sa.HomeLoanPlanID = " + planid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and ta.Years = " + years + " and Display = 1 and hl.LangID= " + langid + " and cl.LangID = " + langid + "");
        }
        public DataTable getHomeLoanMorgage(int cateid, int planid, string balance, int years, int langid)
        {
            return db.exeSelect("Select * from HomeLoan sa join HomeLoanLang hl on sa.HomeLoanID = hl.HomeLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableHomeLoanMortgageInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.HomeLoanCategoryID =" + cateid + " and sa.HomeLoanPlanID = " + planid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and ta.Years = " + years + " and Display = 1 and hl.LangID= " + langid + " and cl.LangID = " + langid + "");
        }
        public DataTable getHomeLoanByProvider(int cateid, int planid, string balance, int years, int CompanyID, int langid)
        {
            return db.exeSelect("Select * from HomeLoan sa join HomeLoanLang hl on sa.HomeLoanID = hl.HomeLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableHomeLoanEquityInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.HomeLoanCategoryID =" + cateid + " and sa.HomeLoanPlanID = " + planid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and ta.Years = " + years + " and Display = 1 and hl.LangID= " + langid + " and cl.LangID = " + langid + " and sa.CompanyID = " + CompanyID + "");
        }
        public DataTable getHomeLoanMorgageByProvider(int cateid, int planid, string balance, int years, int CompanyID, int langid)
        {
            return db.exeSelect("Select * from HomeLoan sa join HomeLoanLang hl on sa.HomeLoanID = hl.HomeLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableHomeLoanMortgageInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.HomeLoanCategoryID =" + cateid + " and sa.HomeLoanPlanID = " + planid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and ta.Years = " + years + " and Display = 1 and hl.LangID= " + langid + " and cl.LangID = " + langid + " and sa.CompanyID = " + CompanyID + "");
        }
        public DataTable getHomeLoanByID(int HomeLoanID, int langid)
        {
            return db.exeSelect("select * from HomeLoan ca join HomeLoanLang hl on ca.HomeLoanID = hl.HomeLoanID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.HomeLoanID = " + HomeLoanID + " and cl.LangID = " + langid + " and hl.LangID = " + langid + "");
        }
        public DataTable getHomeLoanByIDManager(int HomeLoanID, int langid)
        {
            return db.exeSelect("select * from HomeLoan ca join HomeLoanLang hl on ca.HomeLoanID = hl.HomeLoanID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where ca.HomeLoanID = " + HomeLoanID + " and cl.LangID = " + langid + " and hl.LangID = " + langid + "");
        }
        public DataTable getallhomeloancate(int langid)
        {
            return db.exeSelect("Select * from HomeLoanCategory h join HomeLoanCategoryLang hl on h.HomeLoanCategoryID = hl.HomeLoanCategoryID where LangID = " + langid + "");
        }
        public DataTable gethomeloamtrustrelati(int cateid, int CompanyID, int HomeLoanID, int langid)
        {
            return db.exeSelect("select * from HomeLoan ca join HomeLoanLang hl on ca.HomeLoanID = hl.HomeLoanID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.CompanyID = " + CompanyID + " and ca.HomeLoanID != " + HomeLoanID + " and ca.HomeLoanCategoryID = " + cateid + " and Display = 1 and cl.LangID = " + langid + " and hl.LangID = " + langid + "");
        }
        public DataTable getall(int cateid, int langid)
        {
            return db.exeSelect("Select * from HomeLoan sa join HomeLoanLang hl on sa.HomeLoanID = hl.HomeLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join Language la on hl.LangID = la.ID  where sa.HomeLoanCategoryID = " + cateid + " and hl.LangID = " + langid + " and cl.LangID = " + langid + "");
        }
        public int UpdateHomeLoan(HomeLoanTrustModels models)
        {
            return db.exeUpdate("update HomeLoan set HomeLoanName = N'" + models.HomeLoanName + "', MinimumBalance=" + double.Parse(models.MinimumBalance) + ",MaximumBalance=" + double.Parse(models.MaximumBalance) + ",HomeLoanPlanID=" + models.HomeLoanPlanID + ",CompanyID= " + models.CompanyID + ",Display='" + models.Display + "' where HomeLoanID = " + models.HomeLoanID + "");
        }
        public int InsertHomeLoanTrust(HomeLoanTrustModels models)
        {
            return db.exeUpdate("insert into HomeLoan values (N'" + models.HomeLoanName + "'," + models.CompanyID + ",1," + double.Parse(models.MinimumBalance) + "," + double.Parse(models.MaximumBalance) + "," + models.HomeLoanPlanID + ",'" + models.Display + "')");
        }
        public int InsertHomeLoanMortgage(HomeLoanTrustModels models)
        {
            return db.exeUpdate("insert into HomeLoan values (N'" + models.HomeLoanName + "'," + models.CompanyID + ",2," + double.Parse(models.MinimumBalance) + "," + double.Parse(models.MaximumBalance) + "," + models.HomeLoanPlanID + ",'" + models.Display + "')");
        }
        public int DeleteHomeLoan(int HomeLoanID)
        {
            return db.exeUpdate("Delete from HomeLoan where HomeLoanID = " + HomeLoanID + "");
        }
        public int UpdateHomeLoanLang(HomeLoanTrustModels models, int langid)
        {
            return db.exeUpdate("update HomeLoanLang set HomeLoanName = N'" + models.HomeLoanName + "', Benefit = N'" + models.Benefit + "',Description=N'" + models.Description + "',FeeContent=N'" + models.FeeContent + "',MoreInfo=N'" + models.MoreInfo + "' where HomeLoanID = " + models.HomeLoanID + " and LangID=" + langid + "");
        }
        public int InsertHomeLoanLang(HomeLoanTrustModels models, int langid)
        {
            return db.exeUpdate("insert into HomeLoanLang values (" + models.HomeLoanID + "," + langid + ",N'" + models.HomeLoanName + "',N'" + models.Benefit + "',N'" + models.Description + "',N'" + models.FeeContent + "',N'" + models.MoreInfo + "')");
        }
        public int DeleteHomeLoanLang(int HomeLoanID)
        {
            return db.exeUpdate("Delete from HomeLoanLang where HomeLoanID = " + HomeLoanID + "");
        }

        public int DeleteHomeLoanApply(int HomeLoanApplyID)
        {
            return db.exeUpdate("Delete from HomeLoanApply where HomeLoanApplyID = " + HomeLoanApplyID + "");
        }
        public DataTable getcontractbyid(int HomeLoanapplyid)
        {
            return db.exeSelect("select * from HomeLoanApply ca join [HomeLoan] c on ca.HomeLoanID = c.HomeLoanID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.HomeLoanApplyID = " + HomeLoanapplyid + " and cl.LangID = 1");
        }
        public DataTable getcontractbycateid(int cateid)
        {
            return db.exeSelect("select * from HomeLoanApply ca join [HomeLoan] c on ca.HomeLoanID = c.HomeLoanID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and c.HomeLoanCategoryID = " + cateid + " and cl.LangID = 1");
        }
        public DataTable getcontractbystatuscount(int cateid, int statusid)
        {
            return db.exeSelect("select * from HomeLoanApply ca join [HomeLoan] c on ca.HomeLoanID = c.HomeLoanID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID and  co.Active = 1 and c.HomeLoanCategoryID = " + cateid + " and ca.StatusID = " + statusid + " and cl.LangID = 1");
        }
        public int HomeLoanApply(int HomeLoanID, HomeLoanApplyModels app)
        {
            return db.exeUpdate("insert into HomeLoanApply(HomeLoanID,CustomerName,CustomerEmail,CustomerPhone,CustomerIncome,CustomerDOB,StatusID) values (" + HomeLoanID + ",N'" + app.CustomerName + "',N'" + app.CustomerEmail + "',N'" + app.CustomerPhone + "',N'" + app.CustomerIncome + "',N'" + app.CustomerDOB + "'," + app.StatusID + ")");
        }
        public int UpdateStatus(HomeLoanApplyModels HomeLoan)
        {
            return db.exeUpdate("update HomeLoanApply set StatusID = " + HomeLoan.StatusID + " where HomeLoanApplyID = " + HomeLoan.HomeLoanApplyID + "");
        }

    }
}
